﻿if exists (select * from sysobjects where id = object_id(N'pBUGLOGUsersUpdate') and sysstat & 0xf = 4) drop procedure pBUGLOGUsersUpdate 
GO

-- Updates a record in the [dbo].[Users] table.
-- Concurreny is supported by using checksum method.
CREATE PROCEDURE pBUGLOGUsersUpdate
    @pk_UserID int,
    @p_UserName varchar(25),
    @p_FirstName varchar(25),
    @p_LastName varchar(25),
    @p_Department varchar(50),
    @p_Address varchar(250),
    @p_phoneNumber varchar(50),
    @p_EmailAddress nvarchar(30),
    @p_RegistrationDate smalldatetime,
    @p_RoleID int,
    @p_prevConValue nvarchar(4000),
    @p_force_update  char(1)
AS
DECLARE
    @l_newValue nvarchar(4000),
    @return_status int,
    @l_rowcount int
BEGIN
-- Check whether the record still exists before doing update
    IF NOT EXISTS (SELECT * FROM [dbo].[Users] WHERE [UserID] = @pk_UserID)
        RAISERROR ('Concurrency Error: The record has been deleted by another user. Table [dbo].[Users]', 16, 1)

    -- If user wants to force update to happen even if 
    -- the record has been modified by a concurrent user,
    -- then we do this.
    IF (@p_force_update = 'Y')
        BEGIN

            -- Update the record with the passed parameters
            UPDATE [dbo].[Users]
            SET 
            [UserName] = @p_UserName,
            [FirstName] = @p_FirstName,
            [LastName] = @p_LastName,
            [Department] = @p_Department,
            [Address] = @p_Address,
            [phoneNumber] = @p_phoneNumber,
            [EmailAddress] = @p_EmailAddress,
            [RegistrationDate] = @p_RegistrationDate,
            [RoleID] = @p_RoleID
            WHERE [UserID] = @pk_UserID

            -- Make sure only one record is affected
            SET @l_rowcount = @@ROWCOUNT
            IF @l_rowcount = 0
                RAISERROR ('The record cannot be updated.', 16, 1)
            IF @l_rowcount > 1
                RAISERROR ('duplicate object instances.', 16, 1)

        END
    ELSE
        BEGIN
            -- Get the checksum value for the record 
            -- and put an update lock on the record to 
            -- ensure transactional integrity.  The lock
            -- will be release when the transaction is 
            -- later committed or rolled back.
            Select @l_newValue = CAST(BINARY_CHECKSUM([UserID],[UserName],[FirstName],[LastName],[Department],[Address],[phoneNumber],[EmailAddress],[RegistrationDate],[RoleID]) AS nvarchar(4000)) 
            FROM [dbo].[Users] with (rowlock, holdlock)
            WHERE [UserID] = @pk_UserID


            -- Check concurrency by comparing the checksum values
            IF (@p_prevConValue = @l_newValue)
                SET @return_status = 0     -- pass
            ElSE
                SET @return_status = 1     -- fail

            -- Concurrency check passed.  Go ahead and 
            -- update the record
            IF (@return_status = 0)
                BEGIN

                    UPDATE [dbo].[Users]
                    SET 
                    [UserName] = @p_UserName,
                    [FirstName] = @p_FirstName,
                    [LastName] = @p_LastName,
                    [Department] = @p_Department,
                    [Address] = @p_Address,
                    [phoneNumber] = @p_phoneNumber,
                    [EmailAddress] = @p_EmailAddress,
                    [RegistrationDate] = @p_RegistrationDate,
                    [RoleID] = @p_RoleID
                    WHERE [UserID] = @pk_UserID

                    SET @l_rowcount = @@ROWCOUNT
                    IF @l_rowcount = 0
                        RAISERROR ('The record cannot be updated.', 16, 1)
                    IF @l_rowcount > 1
                        RAISERROR ('duplicate object instances.', 16, 1)

                END
            ELSE
            -- Concurrency check failed.  Inform the user by raising the error
                RAISERROR ('Concurrency Error: The record has been updated by another user. Table [dbo].[Users]', 16, 1)

        END
END

